package jpave.database;
import util.methods.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.StringTokenizer;
import java.util.Arrays;
import java.util.Vector;
import java.util.Collections;
import java.util.Iterator;

import util.Debug.ErrorReport;
import jpave.database.OverviewDBWrapper;
import jpave.annotator.CoreMain;
import jpave.dataholders.AssemblyData;
import jpave.dataholders.BlastHitData;
import jpave.dataholders.MainData;
import jpave.dataholders.ContigData;

import jpave.dataholders.SNPData;
import jpave.dataholders.SequenceData;
import jpave.dataholders.CodingRegion;
import jpave.query_interface.BasicQueryTab;
import jpave.query_interface.JPaveMain;

public class JPaveDBWrapper 
{	
	// a new object is created for almost every query
    public JPaveDBWrapper ( Connection inConnection, String inOnlyAssemblyID  ) 
    		throws Exception
    {
        dbConnection = inConnection;
        strAssemblyID = inOnlyAssemblyID;
        
        if ( StringHelpers.isEmpty(strAssemblyID) ) {
        		System.err.println("Internal error: no assembly ID for JPaveDBWrapper");
        		return;
        }     
        
        // See what data the assembly has been annotated with  
		try {
			Statement stmt = dbConnection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT AID " +
					"FROM assembly WHERE assemblyid= '" + strAssemblyID + "'"); 
			if (rs.next()) AID = rs.getInt("AID");
			else CoreMain.die("Cannot get assembly ID");
			
		} catch (SQLException e) { // fails if the table doesn't exist
			System.err.println("Error: old database schema");
			throw e;
		} 
    }

    public void commitChanges ( ) throws Exception
    {
        //dbConnection.commit();
    }
    
	public void close ( ) throws Exception
	{
	   dbConnection.close();
	}
    
    // creates lines for Jpave Overview and sets various flags (see setFlags)
    public boolean overview (int AID, Vector<String> lines ) throws Exception
    {
        OverviewDBWrapper viewObj = new OverviewDBWrapper(dbConnection);
        viewObj.overview(lines);
        return true;
    }
    
    static public int [] getLibCounts(Connection conn, String [] incLibNames) {
	    	if(incLibNames == null || incLibNames.length == 0) return null;
	    	int [] retVal = new int[incLibNames.length];
	    	Statement stmt = null;
	    	
	    	try {
	    		stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
	
	    		ResultSet rset = stmt.executeQuery("SELECT libid, libsize FROM library");
	    		while(rset.next()) {
	    			String libName = rset.getString("libid");
	    			for(int x=0; x<incLibNames.length; x++) {
	    				if(incLibNames[x].equals(libName)) {
	    					retVal[x] = rset.getInt("libsize");
	    					x = incLibNames.length;
	    				}
	    			}
	    		}
	    	} catch(Exception e) {
	    		ErrorReport.reportError(e);
	    	}
	    	return retVal;
    }
    
    static public String [] getAnnoTypes(Connection inConnection, String inAssemblyID) throws Exception
    {
	    	Statement stmt = null;
	    	Vector<String> retVal = new Vector<String> ();
	    	String type, taxo;
	    	
	    	try {
				stmt = inConnection.createStatement( 
						ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
		            
		            String strQ = "SELECT pja_databases.dbtype, pja_databases.taxonomy FROM pja_databases, assembly " +
		            			 "WHERE pja_databases.AID = assembly.AID AND assembly.assemblyid = '" +
		            			 inAssemblyID + "'";
		            ResultSet rset = stmt.executeQuery(strQ);
		            while(rset.next()) {
		            		retVal.add(type = rset.getString(1));
		            		retVal.add(taxo = rset.getString(2));
		            }
		            rset.close();
		            stmt.close();
		            
	    	} catch(Exception e) {
	    		ErrorReport.reportError(e);
	    	}
	    	return retVal.toArray(new String [0]);
    }
    
    static public Vector<String> getSpecies(Connection inConnection, String inAssemblyID) throws Exception
    {
    		Statement stmt = null;
    		Vector<String> retVal = new Vector<String> ();
    	
    		try {
			stmt = inConnection.createStatement( 
					ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
	            
	            String strQ = "SELECT DISTINCT pja_db_unique_hits.species FROM pja_db_unique_hits, assembly " +
	            			 "WHERE pja_db_unique_hits.AID = assembly.AID AND assembly.assemblyid = '" +
	            			 inAssemblyID + "' ORDER BY pja_db_unique_hits.species ASC";
	            ResultSet rset = stmt.executeQuery(strQ);
	            while(rset.next()) {
	            		retVal.add(rset.getString(1).trim());
	            }
	            rset.close();
	            stmt.close();         
	    	} catch(Exception e) {
	    		ErrorReport.reportError(e);
	    	}  	
	    	return retVal;
    }
    
  
    ////////////////////// CAPandPRAHP methods ////////////////////////////
	public void crossIndexCAP3Output ( ContigData theContig ) throws Exception
	{
		final int CLONE_ID_IDX = 1;	
		final int QUALITY_IDX = 2;		
		final int CONTIG_ID_IDX = 3;		
		
		// Build up a string of the original contig names and ESTs
		String strESTs = theContig.getListOfSequenceNames ("' , '" );
		
		Statement stmt = dbConnection.createStatement(
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		
		String strQuery = "SELECT clone.cloneid, clone.quality, " +
				"contclone.contigid  " +
				"FROM clone JOIN (contclone, contig) " +
				"ON (contclone.cloneid = clone.cloneid " +
				"AND contclone.contigid = contig.contigid) " +
				"WHERE clone.cloneid IN ( '" + strESTs + "' ) " +
				"AND contig.AID = " + AID;

		ResultSet rset  = stmt.executeQuery(strQuery);		
		
		while( rset.next() )
		{	
			String cloneid = rset.getString( CLONE_ID_IDX );
			
			Vector<Integer> qualVect = getIntVectorFromReader (rset.getCharacterStream( QUALITY_IDX ) );
			String contigID = rset.getString( CONTIG_ID_IDX );
						
			// Cross index back to the clone's object in the contig
			SequenceData curClone = theContig.getSequenceByName( cloneid );
			
			if ( curClone.isReverseComplement() )
				Collections.reverse ( qualVect );

			curClone.padAndSetQualities ( qualVect );
			
			curClone.setOldContig( contigID );
		}
		rset.close();
		stmt.close();		
	}
	/**
	 * SessionData: for CAPorPhrap
	 * Loads the input clones IDs.  Returns the resulting clones as a Vector of SequenceData.
	 * The clones are in an arbitrary order.
	 */
	public Vector<SequenceData> loadClones ( Iterator<String> theCloneIDs ) 
		throws Exception
	{
        Statement stmt = null;
        ResultSet rset = null;
             
        try
        {
            Vector<SequenceData> out = new Vector<SequenceData> ();
            String strCloneIDs = StringHelpers.join( theCloneIDs, "', '" );
            stmt = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
            		ResultSet.CONCUR_READ_ONLY);
            String strQ = "SELECT clone.cloneid, clone.sequence, clone.quality, " +
						  "clone.length, clone.libid " +
						  "FROM clone " +
						  "WHERE clone.cloneid IN ( '" + strCloneIDs + "' )";
            rset = stmt.executeQuery( strQ );
            
    			while( rset.next() )
    			{		
    				SequenceData curClone = new SequenceData ("EST");
    	
    				String cloneid = rset.getString( "clone.cloneid" );
    				curClone.setName ( cloneid );
    			
    				int nSeqLen = rset.getInt( "clone.length" );
    			
    				Vector<Integer> qualVect = getIntVectorFromReader ( 
    						rset.getCharacterStream( "clone.quality" ) );

    				if ( nSeqLen != qualVect.size() )
    					throw new RuntimeException ( 
    					"Error: Failed to parse the correct number of quality values.");	
    			
    				String seqString = ( getStringFromReader (
    					rset.getCharacterStream( "clone.sequence" ) ) ).trim();
    				seqString = SequenceData.normalizeBases( seqString, '*', '.' );
 
    				if ( nSeqLen != seqString.length () )
    					throw new RuntimeException ( 
    						"Error: Failed to parse the correct number of bases." );	
    			
    				curClone.compAndSet ( seqString, qualVect, false );
    			                
    				out.add( curClone );		
    			}
    			return out;
        }
        catch(Exception e) {
        		ErrorReport.reportError(e,  "Error: reading database loadClones");
        		throw e;
        }
        finally 
        {
            if ( rset != null ) rset.close();
            if ( stmt != null ) stmt.close();
        }
	}
	// XXX
	public String getCAPparams() 
	{
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = dbConnection.createStatement(
            		ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            // ASM_params are (pname, pvalue) pairs
            String strQ = "SELECT pvalue FROM ASM_params " + 
            		" WHERE pname=\"RECAP_ARGS\" and AID=" + AID;
            rs = stmt.executeQuery ( strQ );
            if ( !rs.next() ) {
            		System.err.println("Error: could not load CAP params AID=" + AID);
            		return null;
            }
            String cap =  rs.getString("pvalue");
      		if ( stmt != null ) stmt.close();
      		if (rs != null) rs.close();
      		return cap;
        }
        catch ( SQLException err ) {
        		ErrorReport.reportError(err, "Error: reading CAPparms");
        }    
        return null;
	}
	///////////////////////// Basic Search Methods /////////////////////////////////
	 /**
     * SessionData: Used by basic search to look for contig IDs by a name pattern
     */
    public ArrayList<Object []> loadContigIDsInAssembly ( String searchStr, BasicQueryTab qTab ) throws Exception
    {
        Statement stmt = null;
        int limit = qTab.getResultLimit();
        String [] lSetNames = qTab.getLibraryNames();
        String [] uSetNames = qTab.getContigSetNames();
        
        boolean hasKEGG = qTab.getParentFrame().hasKEGG();
        boolean hasPFAM = qTab.getParentFrame().hasPFAM();
        boolean hasEC   = qTab.getParentFrame().hasEC();
        
        try {
            stmt = dbConnection.createStatement(
            		ResultSet.TYPE_FORWARD_ONLY,
            		ResultSet.CONCUR_READ_ONLY);
            stmt.setFetchSize(Integer.MIN_VALUE); 
        	
            String fields = 	"contig.contigid,   " +
								"contig.bestmatchid, pja_db_unique_hits.description," +
								"contig.notes,contig.totalexp";
            
            if(hasKEGG)
            	fields += ", pja_db_unique_hits.kegg";
            else
            	fields += ", NULL";
            
            if(hasPFAM)
            	fields += ", pja_db_unique_hits.pfam";
            else
            	fields += ", NULL";
            
            if(hasEC)
            	fields += ", pja_db_unique_hits.ec";
            else
            	fields += ", NULL";
           
            if(lSetNames != null)
            	for(int x=0; x<lSetNames.length; x++)
            		fields += ", contig.LN__" + lSetNames[x];
            
            if(uSetNames != null)
            	for(int x=0; x<uSetNames.length; x++)
            		fields += ", contig.LN__" + uSetNames[x];
            
            String strQuery = 	"SELECT " + fields +
								" FROM contig" +
								" LEFT JOIN pja_db_unitrans_hits ON pja_db_unitrans_hits.PID = contig.PID" +
								" LEFT JOIN pja_db_unique_hits ON pja_db_unique_hits.DUHID = pja_db_unitrans_hits.DUHID" +
								" WHERE " +
								" contig.AID = " + AID +  
								" AND contig.contigid LIKE '" + searchStr + "'" +
            					" GROUP BY contig.contigid";
            strQuery += " LIMIT " + limit;
            ResultSet rset = stmt.executeQuery( strQuery );
            
            ArrayList<Object []> retVal = new ArrayList<Object []> ();
            int numSetFields = ((lSetNames == null)?0:lSetNames.length) + ((uSetNames==null)?0:uSetNames.length);
            int numStaticFields = 8;
            while( rset.next() ) {
	            	Object [] buffer = new Object[numStaticFields + numSetFields];
	    			buffer[0] = rset.getString(1);
	    			buffer[1] = rset.getString(2);
	    			buffer[2] = rset.getString(3);
	    			buffer[3] = rset.getString(4);
	    			buffer[4] = rset.getInt(5);
	    			buffer[5] = rset.getString(6);
	    			buffer[6] = rset.getString(7);
	    			buffer[7] = rset.getString(8);
	    			for(int x=0; x<numSetFields; x++)
	    				buffer[x+numStaticFields] = rset.getDouble(x+numStaticFields+1);
	    			retVal.add(buffer);
	    		}
	
	    		return retVal;
        }
        catch(Exception e) {
	        	ErrorReport.reportError(e,"Error: reading database loadContigIDsInAssembly");
	        	throw e;
        }
        finally {
            if ( stmt != null ) stmt.close();
        }
    }    
	 /**
     * SessionData: Used by basic search to look for contig Notes
     */
    // Basic Sequence Query
    public ArrayList<Object []> loadContigsByNotesInAssembly ( String searchStr, BasicQueryTab qTab ) throws Exception
    {
        Statement stmt = null;

        int limit = qTab.getResultLimit();
        String [] lSetNames = qTab.getLibraryNames();
        String [] uSetNames = qTab.getContigSetNames();
        
        boolean hasKEGG = qTab.getParentFrame().hasKEGG();
        boolean hasPFAM = qTab.getParentFrame().hasPFAM();
        boolean hasEC   = qTab.getParentFrame().hasEC();

        try {
            stmt = dbConnection.createStatement(
            		ResultSet.TYPE_FORWARD_ONLY,
            		ResultSet.CONCUR_READ_ONLY);
            stmt.setFetchSize(Integer.MIN_VALUE); 

            String fields = 	"contig.contigid, " +
								"contig.bestmatchid, pja_db_unique_hits.description," +
								"contig.notes, contig.totalexp";
            
            if(hasKEGG)
            	fields += ", pja_db_unique_hits.kegg";
            else
            	fields += ", NULL";
            
            if(hasPFAM)
            	fields += ", pja_db_unique_hits.pfam";
            else
            	fields += ", NULL";
            
            if(hasEC)
            	fields += ", pja_db_unique_hits.ec";
            else
            	fields += ", NULL";

            if(lSetNames != null)
            	for(int x=0; x<lSetNames.length; x++)
            		fields += ", contig.LN__" + lSetNames[x];

            if(uSetNames != null)
            	for(int x=0; x<uSetNames.length; x++)
            		fields += ", contig.LN__" + uSetNames[x];

			String strQuery = 	"SELECT " + fields +
								" FROM contig " + 
								" LEFT JOIN contclone ON contig.CTGID = contclone.CTGID" +
								" LEFT JOIN pja_db_unitrans_hits ON pja_db_unitrans_hits.PID = contig.PID" +
								" LEFT JOIN pja_db_unique_hits ON pja_db_unique_hits.DUHID = pja_db_unitrans_hits.DUHID" +
								" WHERE" +
								" contig.AID = " + AID +  
								" AND contig.notes LIKE '" + searchStr + "'" + 
								" GROUP BY contig.contigid";
            strQuery += " LIMIT " + limit;
          
            ResultSet rset = stmt.executeQuery( strQuery );
            
            ArrayList<Object []> retVal = new ArrayList<Object []> ();
            int numSetFields = ((lSetNames == null)?0:lSetNames.length) + ((uSetNames==null)?0:uSetNames.length);
            int numStaticFields = 8;
	    		while( rset.next() )
	    		{	
	    			String note = rset.getString(4);
	    			if(note.length()>0) {
	    	        		Object [] buffer = new Object[numSetFields + numStaticFields];
	        			buffer[0] = rset.getString(1);
	        			buffer[1] = rset.getString(2);
	        			buffer[2] = rset.getString(3);
	        			buffer[3] = rset.getString(4);
	        			buffer[4] = rset.getInt(5);
	        			buffer[5] = rset.getString(6);
	        			buffer[6] = rset.getString(7);
	        			buffer[7] = rset.getString(8);
	
	        			for(int x=0; x<numSetFields; x++)
	        				buffer[x+numStaticFields] = rset.getDouble(x+numStaticFields+1);
	       			retVal.add(buffer);
	    			}
	    		}
	
	    		return retVal;
        }
        catch(Exception e) {
	        	ErrorReport.reportError(e,"Error: reading database loadContigsByNotesInAssembly");
	        	throw e;
        }
        finally {
            if ( stmt != null )
                stmt.close();
        }
    }    

    /// XXX Basic DB Hit Query by Hit ID
    public ArrayList<Object []> loadContigsByHitIDInAssembly ( 	String searchStr, BasicQueryTab qTab) throws Exception
    {
        Statement stmt = null;
        ArrayList<Object []> retVal = null;
        
        int limit = qTab.getResultLimit();
        if (limit <= 0) limit = 1000;
        int rankLimit = qTab.getRankLimit();
        if (rankLimit <= 0) rankLimit = 1;
        boolean ovBest = qTab.getBestAnno();
        double eValLimit = qTab.getEValLimit();
        
        boolean KEGGSelected = qTab.isKEGGSelected();
        boolean PFAMSelected = qTab.isPFAMSelected();
        boolean ECSelected = qTab.isECSelected();
        
        String KEGGSubStr = qTab.getKEGGSubStr();
        String PFAMSubStr = qTab.getPFAMSubStr();
        String ECSubStr = qTab.getECSubStr();
        
        String [] lSetNames = qTab.getLibraryNames();
        String [] uSetNames = qTab.getContigSetNames();
        String [] annoTypes = qTab.getAnnoTypes();
        String [] annoTaxo = qTab.getAnnoTaxo();
        String [] species = qTab.getSelectedSpecies();
        boolean speciesInclude = qTab.isSpeciesInclude();
        
        boolean hasKEGG = qTab.getParentFrame().hasKEGG();
        boolean hasPFAM = qTab.getParentFrame().hasPFAM();
        boolean hasEC   = qTab.getParentFrame().hasEC();
        
        try {
            stmt = dbConnection.createStatement(
            		ResultSet.TYPE_FORWARD_ONLY,
            		ResultSet.CONCUR_READ_ONLY);
            stmt.setFetchSize(Integer.MIN_VALUE); 
            

            String fields = 	"pja_db_unitrans_hits.contigid, pja_db_unitrans_hits.uniprot_id, " + 
            					"pja_db_unitrans_hits.e_value, pja_db_unique_hits.description, " + 
            					"pja_db_unique_hits.species, pja_db_unique_hits.dbtype, " + 
            					"pja_db_unique_hits.taxonomy, " +
            					"pja_db_unitrans_hits.blast_rank,  pja_db_unitrans_hits.alignment_len," +
            					"pja_db_unitrans_hits.prot_start, pja_db_unitrans_hits.prot_end,  " +
            					"pja_db_unitrans_hits.ctg_start, pja_db_unitrans_hits.ctg_end ";
            
            if(hasKEGG)
            	fields += ", pja_db_unique_hits.kegg";
            else
            	fields += ", NULL";
            
            if(hasPFAM)
            	fields += ", pja_db_unique_hits.pfam";
            else
            	fields += ", NULL";
            
            if(hasEC)
            		fields += ", pja_db_unique_hits.ec";
            else
            		fields += ", NULL";
            
            if(lSetNames != null)
	            	for(int x=0; x<lSetNames.length; x++) 
	            		fields += ", contig.LN__" + lSetNames[x];
            
            if(uSetNames != null)
	            	for(int x=0; x<uSetNames.length; x++) 
	            		fields += ", contig.L__" + uSetNames[x];
            
            String annoDBs = "";
            //null means all are selected
            if(annoTypes != null) {
            	for(int x=0; x<annoTypes.length; x++) {
            		String temp = "pja_db_unitrans_hits.dbtype='" + annoTypes[x] + "' AND pja_db_unitrans_hits.taxonomy='" + annoTaxo[x] + "'";
            		if(annoDBs.length() == 0)
            			annoDBs = "((" + temp + ")";
            		else
            			annoDBs += " OR (" + temp + ")";
            	}
            	if(annoTypes.length > 0)
            		annoDBs += ")";
            }
                   
            String speciesStr = "";
            String boolStr = speciesInclude?"":"NOT ";
            if(species != null && species.length > 0) {
            	speciesStr = boolStr + "(pja_db_unique_hits.species IN ('" + species[0] + "'";
            	for(int x=1; x<species.length; x++)
            		speciesStr += ", '" + species[x] + "'";
            		speciesStr += "))";
            }
            
            String strQuery = 	"SELECT " + fields +
            					" FROM pja_db_unitrans_hits, pja_db_unique_hits, contig" + 
            					" WHERE pja_db_unitrans_hits.DUHID = pja_db_unique_hits.DUHID" +
            					" AND pja_db_unitrans_hits.CTGID = contig.CTGID" +
            					" AND pja_db_unitrans_hits.blast_rank <= " + rankLimit +
            					" AND pja_db_unitrans_hits.uniprot_id LIKE '" + searchStr + "'"; 
            if(eValLimit >= 0)
	            	strQuery += " AND pja_db_unitrans_hits.e_value <= " + eValLimit;
	            
	            if (ovBest) strQuery += " AND pja_db_unitrans_hits.filter_ovbest=1 ";
	
	            if(annoDBs.length() > 0)
	            		strQuery += " AND " + annoDBs;
	            
	            if(speciesStr.length() > 0)
	            		strQuery += " AND " + speciesStr;
				
	            if(hasKEGG && KEGGSelected) {
	            	if(KEGGSubStr.length() == 0)
	            		strQuery += " AND pja_db_unique_hits.kegg <> ''";
	            	else
	            		strQuery += " AND pja_db_unique_hits.kegg LIKE '%" + KEGGSubStr + "%'";
            }
            
            if(hasPFAM && PFAMSelected) {
	            	if(PFAMSubStr.length() == 0)
	            		strQuery += " AND pja_db_unique_hits.pfam <> ''";
	            	else
	            		strQuery += " AND pja_db_unique_hits.pfam LIKE '%" + PFAMSubStr + "%'";
            }
            
            if(hasEC && ECSelected) {
	            	if(ECSubStr.length() == 0)
	            		strQuery += " AND pja_db_unique_hits.ec <> ''";
	            	else
	            		strQuery += " AND pja_db_unique_hits.ec LIKE '%" + ECSubStr + "%'";
            }
            
            strQuery += " ORDER BY pja_db_unitrans_hits.contigid ASC, pja_db_unitrans_hits.blast_rank ASC," + 
            					" pja_db_unitrans_hits.uniprot_id ASC";
            
            strQuery += " LIMIT " + limit;
            ResultSet rset = stmt.executeQuery( strQuery );
            
            retVal = new ArrayList<Object []> ();
            int numSetFields = ((lSetNames == null)?0:lSetNames.length) + ((uSetNames==null)?0:uSetNames.length);
            int numStaticFields = 16;
//        	"Row", "Seq ID", "Hit ID", "Eval",  "Description", "Species", 
    		//  "Type", "Taxonomy", "Rank", "Align Len","Hit Start", "Hit End", "Seq Start", "Seq End"};
	    		while( rset.next() )
	    		{	
	    	        Object [] readBuffer = new Object[numStaticFields + numSetFields];
	    			readBuffer[0] = rset.getString( 1 );
	    			readBuffer[1] = rset.getString( 2 );
	    			readBuffer[2] = rset.getDouble( 3 );    			
	    			readBuffer[3] = rset.getString( 4 );    			
	    			readBuffer[4] = rset.getString( 5 );    			
	    			readBuffer[5] = rset.getString( 6 );    			
	    			readBuffer[6] = rset.getString( 7 );    			
	    			readBuffer[7] = rset.getInt( 8 );    			
	    			readBuffer[8] = rset.getInt( 9 );   			
	    			readBuffer[9] = rset.getInt( 10 );
	    			readBuffer[10] = rset.getInt( 11 );
	    			readBuffer[11] = rset.getInt( 12 );
	    			readBuffer[12] = rset.getInt( 13 );
	    			readBuffer[13] = rset.getString( 14 );
	    			readBuffer[14] = rset.getString( 15 );
	    			readBuffer[15] = rset.getString( 16 );
	    			    			
	    			for(int x=0; x<numSetFields; x++) {
	    				ResultSetMetaData rsmd = rset.getMetaData();
	    				readBuffer[numStaticFields + x] = rset.getInt(numStaticFields+x+1);
	    			}
	    			
	    			retVal.add(readBuffer);
	    		}
	    		return retVal;
        }
        catch(Exception e) {
        		ErrorReport.reportError(e,"Error: reading database loadContigsByUniprotIDInAssembly");
        		throw e;
        }
        finally {
            if ( stmt != null ) stmt.close();
        }
    }    

    // XXX Basic DB Hits Query for by description
    public ArrayList<Object []> loadContigsByHitDescriptionInAssembly ( String searchStr, BasicQueryTab qTab) throws Exception
    {
        Statement stmt = null;
        ArrayList<Object []> retVal = null;

        int limit = qTab.getResultLimit();
        if (limit <= 0) limit = 1000;
        int rankLimit = qTab.getRankLimit();
        if (rankLimit <= 0) rankLimit = 1;
        boolean ovBestAnno = qTab.getBestAnno();
        boolean ovBestEval = qTab.getBestEval();
        double eValLimit = qTab.getEValLimit();
        
        boolean hasKEGG = qTab.getParentFrame().hasKEGG();
        boolean hasPFAM = qTab.getParentFrame().hasPFAM();
        boolean hasEC   = qTab.getParentFrame().hasEC();
        
        boolean KEGGSelected = qTab.isKEGGSelected();
        boolean PFAMSelected = qTab.isPFAMSelected();
        boolean ECSelected = qTab.isECSelected();
        
        String KEGGSubStr = qTab.getKEGGSubStr();
        String PFAMSubStr = qTab.getPFAMSubStr();
        String ECSubStr = qTab.getECSubStr();
        
        String [] lSetNames = qTab.getLibraryNames();
        String [] uSetNames = qTab.getContigSetNames();
        String [] annoTypes = qTab.getAnnoTypes();
        String [] annoTaxo = qTab.getAnnoTaxo();
        String [] species = qTab.getSelectedSpecies();
        boolean speciesInclude = qTab.isSpeciesInclude();
        
        try {
            stmt = dbConnection.createStatement(
            		ResultSet.TYPE_FORWARD_ONLY,
            		ResultSet.CONCUR_READ_ONLY);
            stmt.setFetchSize(Integer.MIN_VALUE); 
       
            String fields = 	"pja_db_unitrans_hits.contigid, pja_db_unitrans_hits.uniprot_id, " + 
            				"pja_db_unitrans_hits.e_value, pja_db_unique_hits.description, " + 
            				"pja_db_unique_hits.species, pja_db_unique_hits.dbtype, " + 
            				"pja_db_unique_hits.taxonomy, pja_db_unitrans_hits.blast_rank," + 
            				"pja_db_unitrans_hits.alignment_len,  pja_db_unitrans_hits.prot_start, " + 
						"pja_db_unitrans_hits.prot_end,  pja_db_unitrans_hits.ctg_start, " +
						"pja_db_unitrans_hits.ctg_end";
            
            if(hasKEGG) fields += ", pja_db_unique_hits.kegg";
            else fields += ", NULL";
            
            if(hasPFAM) fields += ", pja_db_unique_hits.pfam";
            else fields += ", NULL";
            
            if(hasEC) fields += ", pja_db_unique_hits.ec";
            else fields += ", NULL";
           
            if(lSetNames != null)
	            	for(int x=0; x<lSetNames.length; x++) 
	            		fields += ", contig.LN__" + lSetNames[x];

            if(uSetNames != null)
	            	for(int x=0; x<uSetNames.length; x++)
	            		fields += ", contig.L__" + uSetNames[x];
 
            String annoDBs = "";
            //null means all are selected
            if(annoTypes != null) {
            	for(int x=0; x<annoTypes.length; x++) {
            		String temp = "pja_db_unitrans_hits.dbtype='" + annoTypes[x] + "' AND pja_db_unitrans_hits.taxonomy='" + annoTaxo[x] + "'";
            		if(annoDBs.length() == 0)
            			annoDBs = "((" + temp + ")";
            		else
            			annoDBs += " OR (" + temp + ")";
            	}
            	if(annoTypes.length > 0)
            		annoDBs += ")";
            }
            
            String speciesStr = "";
            String boolStr = speciesInclude?"":"NOT ";
            if(species != null && species.length > 0) {
	            	speciesStr = boolStr + "(pja_db_unique_hits.species IN (\"" + species[0] + "\"";
	            	for(int x=1; x<species.length; x++)
	            		speciesStr += ", \"" + species[x] + "\"";
	            	speciesStr += "))";
            }

            String strQuery = 	"SELECT " + fields +
							" FROM pja_db_unitrans_hits, pja_db_unique_hits, contig" + 
            					" WHERE pja_db_unique_hits.description LIKE '" + searchStr + "'" + 
            					" AND pja_db_unique_hits.DUHID = pja_db_unitrans_hits.DUHID" +
            					" AND pja_db_unitrans_hits.CTGID = contig.CTGID" + 
            					" AND pja_db_unitrans_hits.blast_rank <= " + rankLimit;
            if(eValLimit >= 0)
            		strQuery += " AND pja_db_unitrans_hits.e_value <= " + eValLimit;

            if (ovBestAnno) strQuery += " AND pja_db_unitrans_hits.filter_ovbest=1 ";
            if (ovBestEval) strQuery += " AND pja_db_unitrans_hits.filter_best=1 ";

            if(annoDBs.length() > 0) strQuery += " AND " + annoDBs;
            
            if(speciesStr.length() > 0) strQuery += " AND " + speciesStr;
            
            if(hasKEGG && KEGGSelected) {
	            	if(KEGGSubStr.length() == 0)
	            		strQuery += " AND pja_db_unique_hits.kegg <> ''";
	            	else
	            		strQuery += " AND pja_db_unique_hits.kegg LIKE '%" + KEGGSubStr + "%'";
            }
            
            if(hasPFAM && PFAMSelected) {
	            	if(PFAMSubStr.length() == 0)
	            		strQuery += " AND pja_db_unique_hits.pfam <> ''";
	            	else
	            		strQuery += " AND pja_db_unique_hits.pfam LIKE '%" + PFAMSubStr + "%'";
            }
            
            if(hasEC && ECSelected) {
	            	if(ECSubStr.length() == 0)
	            		strQuery += " AND pja_db_unique_hits.ec <> ''";
	            	else
	            		strQuery += " AND pja_db_unique_hits.ec LIKE '%" + ECSubStr + "%'";
            }       
           strQuery += " LIMIT " + limit;
   
            ResultSet rset = stmt.executeQuery( strQuery );
            
            retVal = new ArrayList<Object []> ();
            int numSetFields = ((lSetNames == null)?0:lSetNames.length) + 
            						((uSetNames==null)?0:uSetNames.length);
            int numStaticFields = 16;
            //	"Row", "Seq ID", "Hit ID", "Eval",  "Description", "Species", 
    		//  "Type", "Taxonomy", "Rank", "Align Len","Hit Start", "Hit End", "Seq Start", "Seq End"};
            while( rset.next() )
	    		{	
	    	        Object [] readBuffer = new Object[numStaticFields + numSetFields];
	    			readBuffer[0] = rset.getString( 1 );
	    			readBuffer[1] = rset.getString( 2 );
	    			readBuffer[2] = rset.getDouble( 3 );    			
	    			readBuffer[3] = rset.getString( 4 );    			
	    			readBuffer[4] = rset.getString( 5 );    			
	    			readBuffer[5] = rset.getString( 6 );    			
	    			readBuffer[6] = rset.getString( 7 );    			
	    			readBuffer[7] = rset.getInt( 8 );    			
	    			readBuffer[8] = rset.getInt( 9 );   			
	    			readBuffer[9] = rset.getInt( 10 );
	    			readBuffer[10] = rset.getInt( 11 );
	    			readBuffer[11] = rset.getInt( 12 );
	    			readBuffer[12] = rset.getInt( 13 );
	    			readBuffer[13] = rset.getString( 14 );
	    			readBuffer[14] = rset.getString( 15 );
	    			readBuffer[15] = rset.getString( 16 );
	    			for(int x=0; x<numSetFields; x++)
	    				readBuffer[numStaticFields + x] = rset.getInt(numStaticFields+x+1);
	    			
	    			retVal.add(readBuffer);
	    		}		
    			return retVal;
        }
        catch(Exception e) {
	        	ErrorReport.reportError(e,"Error: reading database loadContigsByUniprotIDInAssembly");
	        	throw e;
        }
        finally {
            if ( stmt != null ) stmt.close();
        }
    }    
    // SessionData: for Basic Search
	public ArrayList<Object []> loadCloneIDsWithContig ( String searchPattern, boolean includeBuried, int limit) throws Exception
	{
        Statement stmt = null;
        ResultSet rset = null;

        try
        {
            stmt = dbConnection.createStatement(
            	ResultSet.TYPE_FORWARD_ONLY,
            	ResultSet.CONCUR_READ_ONLY);
            stmt.setFetchSize(Integer.MIN_VALUE); // supposed to prevent memory hogging on large query
    
            String strQ;
            if(includeBuried)
            	strQ = "SELECT contig.contigid, " +
            			"contclone.cloneid, contclone.buried " +
            			"FROM contig JOIN contclone WHERE contig.contigid = contclone.contigid " +
            			"AND contig.AID = " + AID +   
            			" AND cloneid LIKE '" + searchPattern + "'";
            else
            	strQ = "SELECT contig.contigid, " +
            			"contclone.cloneid, contclone.buried " +
            			"FROM contig JOIN contclone " +
            			"WHERE contig.contigid = contclone.contigid " +
            			"AND contig.AID = " + AID + " " +
            			"AND buried!=1 AND cloneid LIKE '" + searchPattern + "'";
       	
            strQ += " LIMIT " + limit;
 
            rset = stmt.executeQuery( strQ );
            ArrayList<Object []> retVal = new ArrayList<Object []> ();
	    		while( rset.next() )
	    		{	
	                Object [] buffer = new Object[3];
	    			buffer[0] = rset.getString("contig.contigid");
	    			buffer[1] = rset.getString("contclone.cloneid");
	    			buffer[2] = (rset.getInt("contclone.buried") == 1);
	    			retVal.add(buffer);
	    		}
	    		return retVal;
        }
        catch(Exception e) {
	        	ErrorReport.reportError(e, "Error: reading database loadCloneIDsWithContig");
	        	throw e;
        }
        finally 
        {
            if ( rset != null ) rset.close();
            if ( stmt != null ) stmt.close();
        }		
	}
	
    //////////////////////// ClusterViewTab methods ////////////////////////
	//SessionData
	public void loadContigBuriedSequences ( MainData theCluster, boolean loadLocation ) throws Exception
	{
		Iterator<ContigData> iter = theCluster.getContigIterator();
		while ( iter.hasNext() ) {
			ContigData curContig = (ContigData)iter.next();
			if ( loadLocation ) 
				loadBuriedESTsLocationOnlyForContig(curContig);
			else
				loadBuriedESTsForContig(curContig);
			
			theCluster.replaceContig ( curContig.getContigID(), curContig );
		}			
	}
	
	// SessinData which is called by ClusterViewTab.loadESTsAndRun
	public void loadContigUnburiedSequences ( MainData inCluster) throws Exception		
	{		
		// Go through the list of contigs to load the sequences if we don't have them
		Iterator<ContigData> iter = inCluster.getContigIterator();
		while ( iter.hasNext() ) {
			ContigData curContig = (ContigData)iter.next();
			if ( !curContig.areSequencesLoaded ( ) ) {
				ContigData fullContig = loadConsensusAndUnburiedESTs( curContig.getContigID() );
				
				if ( fullContig != null )
					inCluster.replaceContig ( curContig.getContigID(), fullContig );
			}
		}	
	}
	
    private ContigData loadConsensusAndUnburiedESTs( String strContigID ) throws Exception
    {
        ContigData theContig = loadConsensusAndData ( strContigID );
        if ( theContig != null ) {
        		loadUnburiedESTsForContig ( theContig );
        }
        return theContig;
    }
    
    // Loading contig information for display
    // note that the database is queried for columns in the FieldMapper
    private ContigData loadConsensusAndData ( String strContigID ) 
    		throws Exception
    {	
        Statement stmt = null;
        ResultSet rset = null;
        
        ContigData curContig = new ContigData ();
        try
        {          
            // contains all the contigs for this assembly, the number of clones they each have and their best matches
            stmt = dbConnection.createStatement(
            	ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            
            String strQuery = "SELECT CTGID, contigid, consensus, quality, " +
            			"notes, has_ns, consensus_bases, " + 
            			"numclones, frpairs, est_5_prime, est_3_prime, est_loners, " +
            			"snp_count, indel_count, gc_ratio, ";
            
            strQuery +=  "o_coding_start, o_coding_end, o_coding_has_begin, o_coding_has_end, o_frame, " +
                 		"p_coding_start, p_coding_end, p_coding_has_begin, p_coding_has_end, p_frame, " +
                 		"PID, bestmatchid, PIDov, " +
                 		"cnt_pairwise, cnt_swiss, cnt_trembl, cnt_nt, cnt_gi," +
                 		"cnt_species, cnt_gene, cnt_overlap, cnt_taxo  " +                                                      
                 		"FROM contig " +
                 		"WHERE contig.contigid = '" + strContigID + "' " + 
                 		"AND contig.AID = " + AID;

            rset = stmt.executeQuery( strQuery );
            if ( !rset.next() ) {
            		System.err.println("newLoadConsensusAndData: null for " + strContigID);
            		return null;
            }
            int CTGID = rset.getInt("CTGID");
            curContig.setCTGID(CTGID);
            curContig.setContigID( rset.getString( "contigid" ) );
 
            String seqString = ( getStringFromReader (
            		rset.getCharacterStream( "consensus" ) ) ).trim();
            
            Vector<Integer> qualVector = getIntVectorFromReader (
            		rset.getCharacterStream( "quality" ) );

            SequenceData consensus = new SequenceData ("consensus");
            consensus.setName( strContigID );
            consensus.setSequence ( SequenceData.normalizeBases( seqString, '*', '.' ) );
            consensus.padAndSetQualities ( qualVector );
            curContig.setSeqData( consensus );
 
            curContig.setNumSequences( Integer.parseInt(rset.getString( "numclones" )) ); 
            
            if ( rset.getString( "notes" ) != null )
            		curContig.setNotes( rset.getString("notes"));
            
            curContig.setHasNs( Converters.asBool ( rset.getInt( "has_ns" ) ) );
            curContig.setConsensusBases( rset.getInt( "consensus_bases" ) );
            
            curContig.setESTMatePairs( rset.getInt( "frpairs" ) );
            curContig.setEST5Prime( rset.getInt( "est_5_prime" ) );
            curContig.setEST3Prime( rset.getInt( "est_3_prime" ) );
            curContig.setESTLoners( rset.getInt( "est_loners" ) );
            
            curContig.setSNPCount( rset.getInt( "snp_count" ) );
            curContig.setSNPInDels(rset.getInt("indel_count")); 
            curContig.setGCratio(rset.getFloat("gc_ratio")); 
         
            if ( rset.getObject( "PID" ) != null ) {
            		int ctgLen = rset.getInt( "consensus_bases" );
            		curContig.setSeqDataHitList(loadSeqHitDataForCtg(curContig, ctgLen));
            		curContig.setBestMatch(rset.getString("bestmatchid"));
            		curContig.setSwissTremblNTCnt(rset.getInt("cnt_swiss"), 
            				rset.getInt("cnt_trembl"), rset.getInt("cnt_nt"), rset.getInt("cnt_gi")); // CAS 2/6/15
              	curContig.setGeneCntEtc(rset.getInt("cnt_gene"),
              			rset.getInt("cnt_species"), rset.getInt("cnt_overlap"),
              			rset.getInt("cnt_taxo"));
            }
            curContig.setPairwiseCnt(rset.getInt("cnt_pairwise"));
            
            CodingRegion ocoding = loadCodingRegion ( rset, 
		            curContig.getConsensusBases(), CodingRegion.TYPE_LARGEST_ORF,
		            "o_coding_start", "o_coding_has_begin",
		            "o_coding_end", "o_coding_has_end","o_frame" );          	
            curContig.setLargestCoding( ocoding ); 
            
            CodingRegion pcoding = loadCodingRegion ( rset, 
			            curContig.getConsensusBases(), CodingRegion.TYPE_UNIPROT,
			            "p_coding_start", "p_coding_has_begin",
			            "p_coding_end", "p_coding_has_end","p_frame" );          	
            curContig.setProteinCoding( pcoding ); 
            
         	if (doesTableExist(dbConnection, "go_info") && doesTableExist(dbConnection,"pja_gotree"))
         		hasGO=true;
         	else hasGO=false;
        	
            if (hasGO) {
                int cntGO = 0;
                int cntAsn = 0;
                String nameList = "";
                rset = stmt.executeQuery("select gi.gonum,gi.descr,ug.direct from pja_unitrans_go as ug " + 
                		" join go_info as gi on gi.gonum=ug.gonum " + "" +
                				" where ctgid=" + CTGID );
                while (rset.next())
                {
	                	int gonum = rset.getInt(1);
	                	String name = rset.getString(2);
	                	boolean direct = rset.getBoolean(3);
	                	cntGO++;
	                	if (direct)
	                	{
	                		cntAsn++;
	   	           		 	if (nameList=="") nameList = name; 
	   	           		 	else nameList += ";" + name; 
	               		
	                	}
                }
                rset.close();
                rset = stmt.executeQuery("select count(*) from pja_gotree as t " +
                		" join pja_unitrans_go as ug on ug.gonum=t.gonum " +
                		" where ug.ctgid=" + CTGID);
                rset.first();
                int cntGOTree = rset.getInt(1);
                            
                curContig.setCntGO(cntGO);
                curContig.setCntGOTree(cntGOTree);
                curContig.setCntAssignGO(cntAsn);
                curContig.setGOstr(nameList);
                rset.close();
            }
        }
        catch(Exception e) {
        		ErrorReport.reportError(e, "Error: reading database newLoadConsensusAndData");
        		throw e;
        }
        finally 
        {
            if ( rset != null ) rset.close();
            if ( stmt != null ) stmt.close();   
        }
        return curContig;
    }	

	/**
	 * Called if contig contains only unburied ESTs, and user wants to see alignment
	 * for buried ESTs as well
	 */
	private void loadBuriedESTsForContig( ContigData theContig ) throws Exception {

		boolean tGapsExist=doesFieldExist(dbConnection, "contclone", "tgaps");
		boolean extrasExist=doesFieldExist(dbConnection, "contclone", "extras");
		
        String tGapField = "";
        if(tGapsExist)
        	tGapField = ", contclone.tgaps";
        else if(extrasExist)
        	tGapField = ", contclone.extras";
        
		Statement stmt = dbConnection.createStatement(
        		ResultSet.TYPE_FORWARD_ONLY,
        		ResultSet.CONCUR_READ_ONLY);
		// supposed to prevent memory hogging on large query
        stmt.setFetchSize(Integer.MIN_VALUE); 
        
		String strQuery = "SELECT clone.cloneid, clone.Sequence," +
				"clone.Quality, clone.Length" + tGapField +
				", contclone.LeftPos, contclone.Orient, contclone.Gaps" +
				", clone.libid, buryclone.parentid " + 
				"FROM clone JOIN contclone ON contclone.cloneid = clone.cloneid " +
				"LEFT JOIN buryclone ON (buryclone.childid = clone.cloneid " + 	
				"AND buryclone.AID = " + AID + ") " +	
				"WHERE contclone.contigid = '" + theContig.getContigID() + "' " +
				" AND contclone.buried=1";
		
		ResultSet rset  = stmt.executeQuery(strQuery);
		int cloneCount = 0;
		while( rset.next() ) {	
			SequenceData curClone = new SequenceData ("EST");

			String cloneid = rset.getString( "clone.cloneid" );
			curClone.setName ( cloneid );

			int nSeqLen = rset.getInt( "clone.Length" );

			boolean bComplement = rset.getString( "contclone.Orient" ).equals ("C");

			//We already know it is buried
			curClone.setBuried( true );

			curClone.setParentName( rset.getString( "buryclone.parentid" ) );
			
			if(tGapsExist) {
				curClone.setTGaps(getIntArrayFromString(rset.getString("contclone.tgaps")));
			}
			else if(extrasExist) {
				curClone.setTGaps(getIntArrayFromString(rset.getString("contclone.extras")));
			}
			else
				curClone.setTGaps(new int[0]);


			Vector<Integer> qualVect = getIntVectorFromReader (
					rset.getCharacterStream( "clone.Quality" ) );

			if ( nSeqLen != qualVect.size() )
			{	
				throw new RuntimeException ( 
					"Error: Failed to parse the correct number of quality values." );		
			}
			String seqString = ( getStringFromReader (rset.getCharacterStream( "clone.Sequence" ) ) ).trim();
				seqString = SequenceData.normalizeBases( seqString, '*', '.' );

			if ( nSeqLen != seqString.length () )
				throw new RuntimeException ( 
					"Error: Failed to parse the correct number of bases." );

			curClone.compAndSet ( seqString, qualVect, bComplement );
			
			// Gaps (gaps that were inserted to match to consensus)
			int[] gapArray = getIntArrayFromReader ( 
					rset.getCharacterStream("contclone.Gaps") );
						
			Arrays.sort ( gapArray );
			
			// Insert all gaps (from alignment) into the clone's data
			for( int i = gapArray.length - 1; i >= 0; --i )
				curClone.insertGapAt ( gapArray[i] + 1 );
						
			// Left position (aligns clone to consensus).  
			// This should always be done after inserting
			// the gaps, since it will change the indexes 
			// to be relative to the reference sequence.
			curClone.setLeftPos ( rset.getInt( "contclone.LeftPos" ) );
			curClone.setSequenceFromTGaps();
          		
			theContig.addSequence( curClone );		
			cloneCount++;
		}
		rset.close();
		stmt.close();

		// count buried children under each top-level parent clone
		for (SequenceData sd : theContig.getAllSequences()) {
			if ( sd.getParentName() == null ) // restrict to top-level parents
				sd.setBuriedChildCount( countBuried( sd.getName(), 
						theContig.getAllSequences() ) );
		}		
	}

	/**
	 * Called if contig contains only unburied ESTs, and user wants to see the
	 * buried ESTs as well, but only by location
	 */
	private void loadBuriedESTsLocationOnlyForContig( ContigData theContig ) throws Exception {
		Statement stmt = dbConnection.createStatement(
        		ResultSet.TYPE_FORWARD_ONLY,
        		ResultSet.CONCUR_READ_ONLY);
		// supposed to prevent memory hogging on large query
        stmt.setFetchSize(Integer.MIN_VALUE); 
		String strQuery = "SELECT clone.cloneid, " +
				"clone.Length" +
				", contclone.LeftPos, contclone.Orient" +
				", buryclone.parentid " + 
				"FROM clone JOIN contclone ON contclone.cloneid = clone.cloneid " +
				"LEFT JOIN buryclone ON (buryclone.childid = clone.cloneid " + 	
				"AND buryclone.AID = " + AID + ") " +	
				"WHERE contclone.contigid = '" + theContig.getContigID() + "' " +
				" AND contclone.buried=1";
		
		ResultSet rset  = stmt.executeQuery(strQuery);
		int cloneCount = 0;
		while( rset.next() ) {	
			SequenceData curClone = new SequenceData ("EST");

			String cloneid = rset.getString( "clone.cloneid" );
			curClone.setName ( cloneid );
			
			//Since we do not load the sequence, we need to store the length
			int nSeqLen = rset.getInt( "clone.Length" );
			curClone.setLength(nSeqLen);

			boolean bComplement = rset.getString( "contclone.Orient" ).equals ("C");

			//We already know it is buried
			curClone.setBuried( true );

			curClone.setParentName( rset.getString( "buryclone.parentid" ) );
			
			//Assuming no TGaps
			curClone.setTGaps(new int[0]);

			curClone.setComplement(bComplement);

			curClone.setLeftPos ( rset.getInt( "contclone.LeftPos" ) );
			
			theContig.addSequence( curClone );		
			cloneCount++;
		}
		rset.close();
		stmt.close();

		// count buried children under each top-level parent clone
		for (SequenceData sd : theContig.getAllSequences()) {
			if ( sd.getParentName() == null ) // restrict to top-level parents
				sd.setBuriedChildCount( countBuried( sd.getName(), 
						theContig.getAllSequences() ) );
		}		
	}
	/**
	 * Loads all sequence data for unburied ESTs
	 * Note: If you want all ESTs, follow this method call with addBuriedESTsForContig
	 */
	private void loadUnburiedESTsForContig ( ContigData theContig ) throws Exception
	{	
		boolean tGapsExist=doesFieldExist(dbConnection, "contclone", "tgaps");
		boolean extrasExist=doesFieldExist(dbConnection, "contclone", "extras");
		
		Statement stmt = dbConnection.createStatement(
        		ResultSet.TYPE_FORWARD_ONLY,
        		ResultSet.CONCUR_READ_ONLY);
		// supposed to prevent memory hogging on large query
        stmt.setFetchSize(Integer.MIN_VALUE); 

        //Check if we have a tgaps field
        String tGapField = "";
        if(tGapsExist) tGapField = ", contclone.tgaps";
        else if(extrasExist) tGapField = ", contclone.extras";
        
		String strQuery = "SELECT clone.cloneid, clone.Sequence," +
				"clone.Quality, clone.Length" +
				", contclone.LeftPos, contclone.Orient, contclone.Gaps" +
				tGapField +
				", clone.libid, contclone.buried , buryclone.parentid " + 
				"FROM clone JOIN contclone ON contclone.cloneid = clone.cloneid " +
				"LEFT JOIN buryclone ON (buryclone.childid = clone.cloneid " + 	
				"AND buryclone.AID = " + AID + ") " +	
				"WHERE contclone.contigid = '" + theContig.getContigID() + "' " +
				" AND contclone.buried!=1";
				
		ResultSet rset  = stmt.executeQuery(strQuery);
		int cloneCount = 0;
		while( rset.next() ) {	
			SequenceData curClone = new SequenceData ("EST");

			String cloneid = rset.getString( "clone.cloneid" );
			curClone.setName ( cloneid );

			int nSeqLen = rset.getInt( "clone.Length" );

			boolean bComplement = rset.getString( "contclone.Orient" ).equals ("C");

			curClone.setBuried( rset.getInt( "contclone.buried" ) == 1 );

			curClone.setParentName( rset.getString( "buryclone.parentid" ) );
			
			if(tGapsExist) {
				curClone.setTGaps(getIntArrayFromString(rset.getString("contclone.tgaps")));
			}
			else if(extrasExist) {
				curClone.setTGaps(getIntArrayFromString(rset.getString("contclone.extras")));
			}
			else
				curClone.setTGaps(new int[0]);

			Vector<Integer> qualVect = getIntVectorFromReader (
					rset.getCharacterStream( "clone.Quality" ) );

			if ( nSeqLen != qualVect.size() )
			{	
				throw new RuntimeException ( 
					"Error: Failed to parse the correct number of quality values." );		
			}
			String seqString = ( getStringFromReader (rset.getCharacterStream( "clone.Sequence" ) ) ).trim();
				seqString = SequenceData.normalizeBases( seqString, '*', '.' );

			if ( nSeqLen != seqString.length () )
				throw new RuntimeException ( 
					"Error: Failed to parse the correct number of bases." );

			curClone.compAndSet ( seqString, qualVect, bComplement );
			
			// Gaps (gaps that were inserted to match to consensus)
			int[] gapArray = getIntArrayFromReader ( 
					rset.getCharacterStream("contclone.Gaps") );
						
			Arrays.sort ( gapArray );
			
			// Insert all gaps (from alignment) into the clone's data
			for( int i = gapArray.length - 1; i >= 0; --i )
				curClone.insertGapAt ( gapArray[i] + 1 );
						
			// Left position (aligns clone to consensus).  
			// This should always be done after inserting
			// the gaps, since it will change the indexes 
			// to be relative to the reference sequence.
			curClone.setLeftPos ( rset.getInt( "contclone.LeftPos" ) );
			curClone.setSequenceFromTGaps();
		
			theContig.addSequence( curClone );		
			cloneCount++;
		}
		rset.close();
		stmt.close();

		// count buried children under each top-level parent clone
		for (SequenceData sd : theContig.getAllSequences()) {
			if ( sd.getParentName() == null ) // restrict to top-level parents
				sd.setBuriedChildCount( countBuried( sd.getName(), 
						theContig.getAllSequences() ) );
		}
		
        //Set the SNP list, if available in the database
		Vector<SNPData> snp = loadSNPData(theContig.getContigID());
        theContig.setSNPs(snp);
	}
	
	// addBuriedESTsForContig, addBuriedESTsLocationOnly, loadUnBuriedESTsForContig
	static private int countBuried( String strParentID, 
			Vector<SequenceData> clones ) {
		int count = 0;
		for ( SequenceData sd : clones ) {
			if ( strParentID.equals( sd.getParentName() ) ) {
				count += countBuried( sd.getName(), clones ) + 1;
			}
		}
		
		return count;
	}
	
	// loadConsensusAndData
	private CodingRegion loadCodingRegion ( ResultSet rset, 
	    		int nBases, int nType, String strBegin, String strHasBegin,
				String strEnd, String strHasEnd,
				String strFrame ) throws Exception
	{
	        if ( rset.getInt( strFrame ) != 0 )
	        {

	        		CodingRegion coding = new CodingRegion ( nType );
	        		coding.setFrame( rset.getInt( strFrame ) );
	        		coding.setBegin( rset.getInt( strBegin ) );
	        		coding.setEnd( rset.getInt( strEnd ) );
	        		coding.setHasBegin( rset.getBoolean(strHasBegin) );
	        		coding.setHasEnd( rset.getBoolean(strHasEnd) );
	        		return coding;
	        }
	        else 
	        		return null;
	}

	/**
	 * Loads pre-calculated SNP data if available
	 * @return Vector of SNPs from the SNP table
	 */
	private Vector<SNPData> loadSNPData ( String contigID)
	{
        Statement stmt = null;
        ResultSet rset = null;
        
        Vector<SNPData> retVal = new Vector<SNPData>();
        try
        {
            // Do a test to see if we have data in the SNP table
            stmt = dbConnection.createStatement(
            	ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String strQ = "SELECT snp.pos, snp.basevars " +                
            				"FROM contig, snp " +
            				"WHERE contig.AID = " + AID + 
            				" AND contig.contigid='" +contigID+"'" +
            				" AND contig.CTGID = snp.CTGID" +
            				" AND snp.snptype = 'Mis'";
            
            rset = stmt.executeQuery ( strQ );
            while(rset.next()) {
	            	SNPData temp = new SNPData(SNPData.TYPE_AMINO_ACIDS);
	            	temp.setMaybeSNP(true);
	            	temp.setPosition(rset.getInt("snp.pos"));
	            	temp.setValuesFromDBBaseVarsField(rset.getString("snp.basevars"));
	            	retVal.add(temp);
            }
            
            if(retVal.isEmpty())
            		return null;
            return retVal;
        }
        catch ( Exception err ) {
            return null;
        }
        finally {
        	if ( stmt != null )
				try {
					stmt.close();
				} catch (SQLException e) {
					ErrorReport.reportError(e,"Error: reading database for loadSNPdata");
				}
        }		
	}
	/**********************************************************************************
	 * Load all DB ids for a given contig in order to align them from the contig display
	 * XXX YYY CAS adding 2/3/11 
	 **********************************************************************************/
	private ArrayList <SequenceData>loadSeqHitDataForCtg ( ContigData ctgData, int ctgLen) throws Exception
	{
        Statement stmt = null;
        ResultSet rset = null;
        ArrayList <SequenceData> hitList= new ArrayList <SequenceData> ();
        try
        {
            stmt = dbConnection.createStatement(
            		ResultSet.TYPE_FORWARD_ONLY,
            		ResultSet.CONCUR_READ_ONLY);
            stmt.setFetchSize(Integer.MIN_VALUE); // supposed to prevent memory hogging on large query
            
            String ctgID = ctgData.getContigID();
    
            String strQ = 	"SELECT " +
            		"pja_db_unique_hits.DUHID, pja_db_unique_hits.hitID, " +
            		"pja_db_unique_hits.description, pja_db_unique_hits.sequence, " + 
            		"pja_db_unique_hits.species, pja_db_unique_hits.dbtype, pja_db_unique_hits.taxonomy, " +
            		
            		"pja_db_unitrans_hits.uniprot_id, pja_db_unitrans_hits.percent_id, " +
            		"pja_db_unitrans_hits.alignment_len," +
            		"pja_db_unitrans_hits.ctg_start, pja_db_unitrans_hits.ctg_end, " +
            		"pja_db_unitrans_hits.prot_start, pja_db_unitrans_hits.prot_end, " +
            		"pja_db_unitrans_hits.e_value,pja_db_unitrans_hits.bit_score,  " +
            		"pja_db_unitrans_hits.cnt_hsps, " +
            		
            		"pja_db_unitrans_hits.blast_rank, pja_db_unitrans_hits.isProtein, pja_db_unitrans_hits.filtered " +
            		
            		"FROM pja_db_unique_hits " +
            		"JOIN pja_db_unitrans_hits " +
            		"WHERE pja_db_unitrans_hits.DUHID = pja_db_unique_hits.DUHID " +
            		"AND   pja_db_unitrans_hits.AID = " + AID + " " +  
            		"AND   pja_db_unitrans_hits.contigid = '" + ctgID + "'";
         
            rset = stmt.executeQuery( strQ );
	    		while( rset.next() )
	    		{	
	    			SequenceData seq = new SequenceData ("DB");
	    			seq.setName( rset.getString( "hitID" ) );
	    			seq.setSequence( rset.getString("sequence"));
	    			
	    			seq.setDBdesc(rset.getString( "description" ) );
	    			seq.setDBspecies(rset.getString( "species" ) );
	    			seq.setDBtype(rset.getString( "dbtype" ) );
	    			seq.setDBfiltered(rset.getInt( "filtered" ) );
	    			
	            	String line = ctgID + "\t";             
	            line += rset.getString( "uniprot_id" ) + "\t";
	            line += rset.getFloat( "percent_id" ) + "\t";
	            line += rset.getInt("alignment_len") + "\t";
	            line += rset.getInt("ctg_start") + "\t";
	            line += rset.getInt("ctg_end") + "\t";
	            line += rset.getInt("prot_start") + "\t";
	            line += rset.getInt("prot_end") + "\t";
	            line += rset.getDouble( "e_value" ) + "\t"; 
	         
	            line += rset.getDouble( "bit_score" ) + "\t"; 
	            line += rset.getInt("cnt_hsps") + "\t";
	            
	            line += rset.getInt( "isProtein" ) + "\t";   
	            line += rset.getInt("blast_rank") + "\t";
	            line += rset.getInt("filtered") + "\t";
	            
	            line += rset.getString( "dbtype" ) + "\t";
	            line += rset.getString( "taxonomy" ) + "\t";
	            line += rset.getString("species") + "\t";
	            line += rset.getString("description") + "\t";
	            BlastHitData hitData = new BlastHitData(BlastHitData.DB_UNITRANS, line);
	            hitData.setCTGID(ctgData.getCTGID());
	            int f1 = hitData.getFrame1(ctgLen); // still need??
	    			seq.setBlastHitData(hitData);
	    			hitList.add(seq);
	    		}
    		return hitList;
        }
        catch(Exception e) {
	        	ErrorReport.reportError(e, "Error: reading database loadHitsSeqforContig");
	        	throw e;
        }
        finally 
        {
            if ( rset != null ) rset.close();
            if ( stmt != null ) stmt.close();
        }		
	}

	/****************************************************************
	 * alignPairwise
	 */
	public BlastHitData loadPairHitData(String ctg1, String ctg2) throws Exception {
	       Statement stmt = null;
	        ResultSet rset = null;
	        String str="0\t0\t0";
	        try
	        {
	            // Do a test to see if we have data in the homology table
	            stmt = dbConnection.createStatement(
	            	ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	            String strQ = "SELECT coding_frame1, coding_frame2, cnt_shared_hits, e_value, percent_id, " +
	            		"cnt_hsps, alignment_len, ctg1_start, ctg1_end, ctg2_start, ctg2_end, " +
	            		"in_self_blast_set, in_translated_self_blast " +
	            		"FROM pja_pairwise " +
	            		"WHERE contig1 = '" + ctg1 + "' and contig2 = '" + ctg2 + "' ";
	            
	            rset = stmt.executeQuery ( strQ );
	            if (rset.next()) {
	            		str = 
	            			ctg1 + "\t" +
	            			ctg2 + "\t" +
	            			rset.getDouble("percent_id") + "\t" + 
	            			rset.getInt("alignment_len") + "\t" + 
	            			rset.getInt("ctg1_start") + "\t" + 
	            			rset.getInt("ctg1_end") + "\t" + 
	            			rset.getInt("ctg2_start") + "\t" + 
	            			rset.getInt("ctg2_end") + "\t" + 
	            			rset.getDouble("e_value") + "\t" + 
	            			rset.getInt("cnt_hsps") + "\t" + 
	            			
	            			rset.getInt("coding_frame1") + "\t" + 
	            			rset.getInt("coding_frame2") + "\t" +
	            			rset.getInt("cnt_shared_hits") + "\t" +
	            			rset.getInt("in_self_blast_set") + "\t" +
	            			rset.getInt("in_translated_self_blast");
	            		 dbConnection.close(); 
	            		 return new BlastHitData(BlastHitData.DB_PAIRWISE, str);	 
	            }
	            // CAS 5/18/15 this bug must have been there forever. It was only doing
	            // ctg1, ctg2 check, and not vice versa
	            strQ = "SELECT coding_frame1, coding_frame2, cnt_shared_hits, e_value, percent_id, " +
	            		"cnt_hsps, alignment_len, ctg1_start, ctg1_end, ctg2_start, ctg2_end, " +
	            		"in_self_blast_set, in_translated_self_blast " +
	            		"FROM pja_pairwise " +
	            		"WHERE contig1 = '" + ctg2 + "' and contig2 = '" + ctg1 + "' ";
	            
	            rset = stmt.executeQuery ( strQ );
	            if (rset.next()) {
	            		str = 
	            			ctg1 + "\t" +
	            			ctg2 + "\t" +
	            			rset.getDouble("percent_id") + "\t" + 
	            			rset.getInt("alignment_len") + "\t" + 
	            			rset.getInt("ctg1_start") + "\t" + 
	            			rset.getInt("ctg1_end") + "\t" + 
	            			rset.getInt("ctg2_start") + "\t" + 
	            			rset.getInt("ctg2_end") + "\t" + 
	            			rset.getDouble("e_value") + "\t" + 
	            			rset.getInt("cnt_hsps") + "\t" + 
	            			
	            			rset.getInt("coding_frame1") + "\t" + 
	            			rset.getInt("coding_frame2") + "\t" +
	            			rset.getInt("cnt_shared_hits") + "\t" +
	            			rset.getInt("in_self_blast_set") + "\t" +
	            			rset.getInt("in_translated_self_blast");
	            		 dbConnection.close(); 
	            		 return new BlastHitData(BlastHitData.DB_PAIRWISE, str);	 
	            }
	            System.err.println("Internal error: no database pair for " + ctg1 + " " + ctg2);
	            return null;
	        }
	        catch ( Exception err ) {
		        	ErrorReport.reportError(err,"Error: reading database for getPairFrames");
		        	return null;
	        }
	        finally {
	        		if ( stmt != null )stmt.close();
	        		if ( rset != null ) rset.close();
	        }
	}
	/*********  Methods for testing what data has been filled in ***********/
	private static boolean doesFieldExist(Connection inConnection, String table, String field)
	{
		// Create a result set 
		Statement stmt;
		try {
			stmt = inConnection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM " + table + " LIMIT 1"); 
			// Get result set meta data 
			ResultSetMetaData rsmd = rs.getMetaData(); 
			int numColumns = rsmd.getColumnCount(); // Get the column names; column indices start from 1 
			for (int i=1; i<numColumns+1; i++) 
			{ 
				String columnName = rsmd.getColumnName(i); // Get the name of the column 
				if(columnName.equalsIgnoreCase(field))
					return true;
			}
		} catch (SQLException e) {
			ErrorReport.reportError(e, "Error: reading database doesFieldExist");
		} 
		return false;
	}
	private static boolean doesTableExist(Connection inConnection, String table)
	{
		try {
			 Statement stmt = inConnection.createStatement();        
             ResultSet rs = stmt.executeQuery("show tables like '" + table + "'");
             return rs.first();
		} catch (SQLException e) {
			ErrorReport.reportError(e, "Error: reading database doesFieldExist");
		} 
		return false;
	}

	/***********      Assembly table ******************
	 * called to initialize each AssemblyData for all listed in the main window
	 */
	static public int getSetAssemblyParams(Connection inConnection,	AssemblyData assm) throws Exception
	{
        Statement stmt = null;
        ResultSet rs = null;
        try {
        	
            stmt = inConnection.createStatement(
            		ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String strQ = "SELECT AID, username, projectpath, assemblydate, annotationdate,assemblyid " +
            		"FROM assembly ";
 
            rs = stmt.executeQuery ( strQ );
            if ( !rs.next() ) {
	            	System.err.println("Error: could not load assembly informaton");
	            	return 0;
            }
            int i = rs.getInt("AID");
            assm.setAID(i);
            assm.setAssemblyID(rs.getString("assemblyid"));
            assm.setAssemblyUser(rs.getString("username"));
            assm.setAssemblyPath(rs.getString("projectpath"));
            assm.setAssemblyDate(rs.getString("assemblydate"));
            assm.setAnnotationDate(rs.getString("annotationdate")); 
            return i;
        }
        catch ( Exception err ) {
	        	ErrorReport.reportError(err, "in getSetAssemblyParams");
        }
        finally {
        	if ( stmt != null ) stmt.close();
        }      
        return 0;
	}
		
	//Functionality to replace a current contig with one generated by cap3/PHRAP
	static public boolean replaceContig(Connection inConnection, ContigData newContig, int sequenceNum)
	{
		int x;
		PreparedStatement stmt;	
		String orient;

		for(x=0; x<newContig.getNumSequences(); x++)
		{
			try {					
				stmt = inConnection.prepareStatement(
						"UPDATE contclone SET orient=?, leftpos= ?, gaps= ?, ngaps= ?, extras='' WHERE cloneid= ?");
				
				orient = newContig.getSequenceAt(x).isReverseComplement() ? "C" : "U";
				stmt.setString(1, orient);
				stmt.setInt(2, newContig.getSequenceAt(x).getLeftPos());
				stmt.setString(3, newContig.getSequenceAt(x).getGapsFromSequence());
				stmt.setInt(4, newContig.getSequenceAt(x).getNumGapsInSequence());
				stmt.setString(5, newContig.getSequenceAt(x).getName());
					
				stmt.executeUpdate();
					
			} catch (SQLException e) {
				ErrorReport.reportError(e, "Error: reading database for replaceContig");
			}
		}
		
		try {
			stmt = inConnection.prepareStatement(
					"UPDATE contig SET consensus= ?, quality= ? WHERE contigid= ?");

			stmt.setString(1, newContig.getSeqData().getSequence());
			stmt.setString(2, newContig.getSeqData().getQualitiesString());
			stmt.setString(3, newContig.getSequenceAt(0).getOldContig());
			
			stmt.executeUpdate();
	
		} catch (SQLException e) {
			ErrorReport.reportError(e, "Error: reading database for replaceContig 2");
		}
		
		return true;
	}	
	
	//	----------------CONVERTERS for CLOB fields --------------- //
	
	static private Vector<Integer> getIntVectorFromReader ( Reader reader ) 
		throws IOException, SQLException 
	{
		Vector<Integer> theVect = new Vector<Integer> ();
		Converters.addIntArray( getIntArrayFromReader ( reader), theVect );
		return theVect;
	}
	
	static private int [] getIntArrayFromString ( String str )
	{
		if(str.equals("")) return null;
		String [] vals = str.split(" ");
		int [] retval = new int[vals.length];
		
		for(int x=0; x<vals.length; x++)
			retval[x] = Integer.parseInt(vals[x]);
		
		return retval;
	}
	
	static private int [] getIntArrayFromReader ( Reader reader ) 
		throws IOException, SQLException 
	{	
		if ( reader == null )
			return new int [0];

		String strString = getStringFromReader ( reader );

		StringTokenizer toker = new StringTokenizer( strString, " \t" );
		int nCount = toker.countTokens();

		int[] IntArray = new int [nCount]; 
		String strInt;
		for ( int i = 0; i < nCount; ++i  )
		{
			strInt = toker.nextToken();
			IntArray[i] = Integer.parseInt( strInt );
		} 
			
		return IntArray;
	}
	
	static private String getStringFromReader ( Reader reader ) 
		throws IOException, SQLException 
	{		
		String strReturn = "";
		char[] chChars = new char [ 2000 ]; 
		int nNumRead = 0;
	
		do 
		{
			// Keep filling the buffer until we exhaust the stream
			nNumRead = reader.read( chChars );
			if ( nNumRead <= 0 )
				break;
			
			// Append to the output string
			strReturn += new String ( chChars, 0, nNumRead );
		} while ( true );
			
		return strReturn;
	}
	 ///////////////////// XXX Methods for Libraries and Contig Sets ////////////////////////////
		/**
		 * Sets display for library Include/Exclude
		 */
		static public String [] getLibs(Connection inConnection, String inAssemblyID, boolean forLibrary) throws Exception
		{
		    Statement stmt = null;	
		    Vector<String> retval = new Vector<String>();
		    boolean hasCtgLibField = false;
		    
		    if(Version.doesFieldExist(inConnection, "library", "ctglib"))
		    		hasCtgLibField = true;
		    
			try {			
				stmt = inConnection.createStatement( 
					ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
	            
	            String strQ = "SELECT library.libid, library.title FROM assemlib, library " +
	            			 "WHERE assemlib.libid=library.libid AND assemlib.assemblyid = '" +
	            			 inAssemblyID + "'";
	        
	            if(hasCtgLibField) {
		            	if(forLibrary)
		            		strQ += " AND library.ctglib = 0"; 
		            	else
		            		strQ += " AND library.ctglib = 1";
	            }
	            
	            if(hasCtgLibField || forLibrary) {
		            	ResultSet rs = stmt.executeQuery(strQ);
					
		    			while(rs.next ())
		    			{
		    				retval.add(rs.getString(1));
		    				retval.add(rs.getString(2));
		    			}
		    			rs.close();    		
	            }
	    			stmt.close();

	    			return (String [])retval.toArray(new String[retval.size()]);
			}
			catch ( Exception err ) {
				ErrorReport.reportError(err, "Error: reading database for getLibs");
	            return null;
	        }
		}
		
		static public String [] getPrefixedColumns(Connection inConnection, String prefix) throws Exception {
		    Statement stmt = null;	
		    Vector<String> retval = new Vector<String>();
		    try {
				stmt = inConnection.createStatement( 
						ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
		            
				ResultSet rs = stmt.executeQuery("SELECT * FROM contig LIMIT 1");
				
				ResultSetMetaData rsmd = rs.getMetaData();
				int numCols = rsmd.getColumnCount();
				for(int x=1; x<=numCols; x++) {
					if(rsmd.getColumnName(x).startsWith(prefix)) {
						retval.add(rsmd.getColumnName(x).substring(prefix.length()));
					}
				}
				
				return retval.toArray(new String [0]);
		    }
			catch ( Exception err ) {
				ErrorReport.reportError(err, "Error: reading database for getLibs");
	            return null;
	        }
		}
		/*******************************************************
		 * Order of titles corresponds with order of pCol as returned by the above methods
		 * CAS 12/28/14
		 */
		static public String [] getPColTitles(Connection inConnection, String [] pCol) throws Exception {
		    Statement stmt = null;	
		    String [] title = new String [pCol.length];
		    for (int i=0; i<pCol.length; i++) title[i]="";
		    	
		    try {
				stmt = inConnection.createStatement( 
						ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
				ResultSet rs = stmt.executeQuery("show tables like 'libraryDE'");
				if (!rs.first()) {
					return title;
				}
			
				for (int i=0; i<pCol.length; i++) {
					String col = "P_" + pCol[i];
					rs = stmt.executeQuery("SELECT title from libraryDE where pCol='" + col + "'");
					if (rs.next()) title[i] = rs.getString(1); 
				}
				return title;
		    }
			catch ( Exception err ) {
				ErrorReport.reportError(err, "Error: reading database for getLibs");
	            return null;
	        }
		}
	    
		static private boolean hasLibs(Connection inConnection, boolean libCounts) {
			Statement stmt = null;
			boolean retVal = true;
			
			int ctgVal = libCounts?0:1;

			try {
				if(!doesFieldExist(inConnection, "library", "ctglib"))
					retVal = false;
				else {
					stmt = inConnection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
					ResultSet rset = stmt.executeQuery("SELECT COUNT(*) FROM library WHERE ctglib = " + ctgVal);
					rset.next();
					if(rset.getInt(1) == 0)
						retVal = false;
					
					rset.close();
					stmt.close();
				}
			} catch(Exception e) {
				ErrorReport.reportError(e);
			}
			return retVal;
		}
		
		static public boolean hasLibs(Connection inConnection) {
			return hasLibs(inConnection, true);		
		}
		
		static public boolean hasContigSet(Connection inConnection) {
			return hasLibs(inConnection, false);
		}
		
		static public boolean hasNs(Connection inConnection) {
			boolean retVal = false;
			Statement stmt;
			try{
				stmt = inConnection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
				
				String strQ = "SELECT COUNT(*) FROM contig WHERE contig.has_ns = 1 LIMIT 1";
				ResultSet rset = stmt.executeQuery(strQ);
				rset.next();
				retVal = (rset.getInt(1) == 1);
				stmt.close();
				rset.close();
			} catch (Exception e) {
				ErrorReport.reportError(e);
			}
			
			return retVal;
		}
		
		static public boolean hasKEGG(Connection inConnection) {
			boolean retVal = false;
			try {
				retVal = doesFieldExist(inConnection, "pja_db_unique_hits", "kegg");
			}
			catch(Exception e) {
				ErrorReport.reportError(e);
			}	
			return retVal;
		}
		
		static public boolean hasPFAM(Connection inConnection) {
			boolean retVal = false;
			try {
				retVal = doesFieldExist(inConnection, "pja_db_unique_hits", "pfam");
			}
			catch(Exception e) {
				ErrorReport.reportError(e);
			}	
			return retVal;
		}
		
		static public boolean hasEC(Connection inConnection) {
			boolean retVal = false;
			try {
				retVal = doesFieldExist(inConnection, "pja_db_unique_hits", "ec");
			}
			catch(Exception e) {
				ErrorReport.reportError(e);
			}
			return retVal;
		}
				
		static public int getNumLibraries(Connection conn, String inAssemblyID, 
				boolean fromExpressionLibraries)
		{
			Statement stmtLib;
		    int retVal = 0;
		    boolean hasCtgLibField = false;
		    String strQ = "";
			try {
				if(doesFieldExist(conn, "library", "ctglib"))
					hasCtgLibField = true;

				stmtLib = conn.createStatement( 
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );

				int libFilter = 1;
				if(fromExpressionLibraries)
					libFilter = 0;

				if(hasCtgLibField)
					strQ = "SELECT COUNT(libid) FROM library WHERE ctglib = " + libFilter;
				else if(fromExpressionLibraries)
					strQ = "SELECT COUNT(libid) FROM library";
				
				if(strQ.length() > 0) {
					ResultSet rsLibCount = stmtLib.executeQuery(strQ); 
		
					if(rsLibCount.next())
						retVal = rsLibCount.getInt(1);
		
					rsLibCount.close();
				}
				if(stmtLib!=null) stmtLib.close();
			} catch (SQLException e) {
				ErrorReport.reportError(e,"Error: reading database for getNumLibraries");
			}
			
			return retVal;
		}
		
		static public boolean hasNoAssembly(Connection conn) {
			try {
				int num = doIntQuery(conn, "SELECT COUNT(*) FROM contig WHERE numclones > 1"); 	
				if (num==0) return true;
				return false;
			}
			catch (Exception e) {
				ErrorReport.reportError(e,"Error: reading database for hasNoAssembly");
				return false;
			}
		}
		static public boolean hasBuried(Connection conn) {
			try {
				int num = doIntQuery(conn, "SELECT COUNT(*) FROM buryclone");	
				if (num > 0) return true;
				return false;
			}
			catch (Exception e) {
				ErrorReport.reportError(e,"Error: reading database for hasBuried");
				return false;
			}
		}
		static public boolean hasMatePairs(Connection conn) {
			try {
				int num = doIntQuery(conn, "SELECT COUNT(*) FROM contig WHERE frpairs > 1 LIMIT 1"); 	
				if (num>0) return true;
				return false;
			}
			catch (Exception e) {
				ErrorReport.reportError(e,"Error: reading database for hasMatePairs");
				return false;
			}
		}
		static public boolean hasPairWise(Connection conn) {
			try {
				int num = doIntQuery(conn, "SELECT COUNT(*) FROM pja_pairwise");
				if (num>0) return true;
				return false;
			}
			catch (Exception e) {
				ErrorReport.reportError(e,"Error: reading database for hasPairWise");
				return false;
			}
		}
		static public boolean hasSelfblast(Connection conn) {
			try {
				int num = doIntQuery(conn, "SELECT COUNT(*) " +                
						"FROM pja_pairwise WHERE in_self_blast_set = 1");
				if (num>0) return true;
				return false;
			}
			catch (Exception e) {
				ErrorReport.reportError(e,"Error: reading database for hasSelfblast");
				return false;
			}
		}
		static public boolean hasTselfblast(Connection conn) {
			try {
				int num = doIntQuery(conn, "SELECT COUNT(*) " +                
						"FROM pja_pairwise WHERE in_translated_self_blast = 1");
				if (num>0) return true;
				return false;
			}
			catch (Exception e) {
				ErrorReport.reportError(e,"Error: reading database for hasTselfblast");
				return false;
			}
		}
		static public boolean hasDBhits(Connection conn) {
			try {
				int num = doIntQuery(conn, "SELECT count(*) FROM contig "
  	                    + "WHERE bestmatchid is not null");
				if (num>0) return true;
				return false;
			}
			catch (Exception e) {
				ErrorReport.reportError(e,"Error: reading database for hasDBhits");
				return false;
			}
		}
		static public boolean hasDE(Connection conn) {
			try {
				boolean rt=false;
				Statement stmt = conn.createStatement();
				ResultSet rset = stmt.executeQuery("SHOW COLUMNS FROM contig");
		           while(rset.next()) {
		               String col = rset.getString(1);
		               if(col.startsWith("P_")) {
		                   rt =true;
		                   break;
		               }
				   }
				stmt.close();
				rset.close();
				return rt;
			}
			catch (Exception e) {
				ErrorReport.reportError(e,"Error: reading database for hasDE");
				return false;
			}
		}
		
		static public boolean isProteinDB(Connection conn) {
			try {
				boolean rt=false;
				Statement stmt = conn.createStatement();
				ResultSet rset = stmt.executeQuery("SHOW COLUMNS FROM assem_msg");
				   while(rset.next()) {
				     String col = rset.getString(1);
				     if(col.equals("peptide")) {
				    	 	rt = true;
				        break;
				     }
				   }
				stmt.close();
				rset.close();
				return rt;
			}
			catch (Exception e) {
				ErrorReport.reportError(e,"Error: reading database for hasDBhits");
				return false;
			}
		}
		static public boolean hasLoc(Connection conn) {
			try {
				boolean rt=false;
				Statement stmt = conn.createStatement();
				ResultSet rset = stmt.executeQuery("SHOW COLUMNS FROM assem_msg");
				   while(rset.next()) {
				     String col = rset.getString(1);
				     if(col.equals("hasLoc")) {
				    	 	rt = true;
				        break;
				     }
				   }
				stmt.close();
				rset.close();
				return rt;
			}
			catch (Exception e) {
				ErrorReport.reportError(e,"Error: reading database for hasLoc");
				return false;
			}
		}
		
		static public int nContigSets (Connection conn) {
			try {
				return doIntQuery(conn, "SELECT COUNT(*) FROM library " +
		                  "JOIN  assemlib ON (assemlib.libid = library.libid) " +
		                  "WHERE library.ctglib=1");	
			}
			catch (Exception e) {
				ErrorReport.reportError(e,"Error: reading database for nContigSets");
				return 0;
			}
		}
		static public int nContigs (Connection conn) {
			try {
				return doIntQuery(conn, "SELECT COUNT(*) FROM contig");	
			}
			catch (Exception e) {
				ErrorReport.reportError(e,"Error: reading database for nContigs");
				return 0;
			}
		}
		
		static public boolean hasGo(Connection conn) {
		    	if (doesTableExist(conn, "go_info") && doesTableExist(conn,"pja_gotree"))
		    		return true;
		    	else return false;
	    }
		
		 static int doIntQuery ( Connection conn, String strQuery ) throws Exception
	    {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery( strQuery );
			rs.next ();
			int x = rs.getInt(1);
			stmt.close();
			rs.close();
			return x;
	    }
    
    // hasGO is still used by annotator
    static public void setFlags(int n, boolean bd, boolean mp, boolean ns, boolean cs, 
    		boolean pw, boolean hitD, boolean hitS, boolean hasS, boolean hasT, 
    		String pp, boolean peptide, boolean noAssm, int cSets, boolean go) {
    
    		hasGO = go;
    	}

	/************************************************************
	 * Private Implementation
	************************************************************/

    private int AID=1;
    private String strAssemblyID = null;
	private Connection dbConnection = null;
	
	static private boolean hasGO = false;
}
